¶

In [2]:
import pandas as pd
import os
import json
from datetime import datetime

def calculate_total_sum(filepaths):
    summary = {}
    grand_total = 0.0

    for path in filepaths:
        if not os.path.exists(path):
            print(f"파일 없음: {path}")
            continue

        df = pd.read_excel(path)

        if "총합 (MB)" not in df.columns:
            print(f"'총합 (MB)' 컬럼 없음: {path}")
            continue

        try:
            total_col = df["총합 (MB)"].fillna("").astype(str)
            total_mb = total_col.apply(lambda x: float(x.replace(",", "")) if x else 0.0).sum()
            file_key = os.path.basename(path)
            summary[file_key] = round(total_mb, 3)
            print(f"✔ {file_key}: {total_mb:,.3f} MB")
            grand_total += total_mb
        except Exception as e:
            print(f"[ERROR] {path} 처리 중 오류: {e}")

    summary["전체 총합 (MB)"] = round(grand_total, 3)

    # JSON 저장
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_path = f"total_storage_summary_{timestamp}.json"
    with open(output_path, "w", encoding="utf-8") as f:
        json.dump(summary, f, indent=2, ensure_ascii=False)

    print(f"\n전체 총합: {grand_total:,.3f} MB")
    print(f"JSON 저장 완료: {os.path.abspath(output_path)}")

if __name__ == "__main__":
    files = [
        "db_datetime_size_summary.xlsx",
        "db_not_size_summary.xlsx",
        "db_empty_datetime_size_summary.xlsx"
    ]
    calculate_total_sum(files)
✔ db_datetime_size_summary.xlsx: 1,583.900 MB
✔ db_not_size_summary.xlsx: 200.316 MB
✔ db_empty_datetime_size_summary.xlsx: 0.000 MB

전체 총합: 1,784.216 MB
JSON 저장 완료: /Users/sc301/Desktop/yhy/project/lagacy_db/total_storage_summary_20250620_164626.json

엑셀 파일 하나로 합치기¶

In [3]:
import pandas as pd

file1 = "db_datetime_size_summary.xlsx"
file2 = "db_not_size_summary.xlsx"
file3 = "db_empty_datetime_size_summary.xlsx"

df1 = pd.read_excel(file1)
df2 = pd.read_excel(file2)
df3 = pd.read_excel(file3)

output_file = "combined_db_summary.xlsx"

with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
    df1.to_excel(writer, sheet_name="datetime 포함", index=False)
    df2.to_excel(writer, sheet_name="용량 추정 불가", index=False)
    df3.to_excel(writer, sheet_name="datetime 없음 추정", index=False)

print(f"엑셀 파일 저장 완료: {output_file}")
엑셀 파일 저장 완료: combined_db_summary.xlsx

JSON 파일 HTML 파일로 변환¶

In [4]:
import json

# 1. JSON 파일 경로
json_file = "DB 용량 통계.json"
html_file = "DB 용량 통계.html"

# 2. JSON 로드
with open(json_file, "r", encoding="utf-8") as f:
    data = json.load(f)

# 3. HTML 초기 구성
html = """
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>DB 용량 요약</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; }
        h2 { color: #333; margin-top: 40px; }
        table { border-collapse: collapse; width: 100%; margin-bottom: 40px; }
        th, td { border: 1px solid #ccc; padding: 8px; text-align: center; }
        th { background-color: #f2f2f2; }
    </style>
</head>
<body>
    <h1>DB 사용량 요약</h1>
"""

# 4. 테이블 단위로 HTML 작성
for table_name, table_data in data.items():
    html += f"<h2>{table_name}</h2>\n"
    html += f"<p><strong>기간:</strong> {table_data.get('startDate')} ~ {table_data.get('endDate')}</p>\n"

    # 연도별
    year_data = table_data.get("year", {})
    if year_data:
        html += "<h3>연도별 사용량</h3>\n<table>\n<tr><th>연도</th><th>용량 (MB)</th></tr>\n"
        for year, val in sorted(year_data.items()):
            html += f"<tr><td>{year}</td><td>{val}</td></tr>\n"
        html += "</table>\n"

    # 월별
    month_data = table_data.get("month", {})
    if month_data:
        html += "<h3>월별 사용량</h3>\n<table>\n<tr><th>월</th><th>용량 (MB)</th></tr>\n"
        for month, val in sorted(month_data.items()):
            html += f"<tr><td>{month}</td><td>{val}</td></tr>\n"
        html += "</table>\n"

    # 주차 데이터 생략 가능

html += "</body>\n</html>"

# 5. HTML 저장
with open(html_file, "w", encoding="utf-8") as f:
    f.write(html)

print(f"HTML 파일로 변환 완료: {html_file}")
HTML 파일로 변환 완료: DB 용량 통계.html

JSON 파일로 저장¶

In [18]:
import pymysql
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from dateutil import parser
import json
import os
from dotenv import load_dotenv

# 날짜 상수
START_DATE_STATIC = datetime(2000, 1, 1)
END_DATE_STATIC = datetime.today()

# .env 파일 로드
load_dotenv()

# DB 연결
def get_connection():
    host = os.getenv("DB_HOST")
    user = os.getenv("DB_USER")
    password = os.getenv("DB_PASSWORD")
    db = os.getenv("DB_NAME")
    port = int(os.getenv("DB_PORT", "3306"))

    if not all([host, user, password, db]):
        raise ValueError("필수 DB 환경 변수가 .env에 누락되어 있습니다.")

    conn = pymysql.connect(
        host=host,
        user=user,
        password=password,
        db=db,
        port=port,
        charset='utf8',
        connect_timeout=60,
        read_timeout=300,
        write_timeout=300
    )
    print(f"\nDB 연결 성공: {db}\n")
    return conn, db

# 테이블 목록
def get_table_list(cursor, db_name):
    cursor.execute(f"SHOW TABLES FROM `{db_name}`")
    return [row[0] for row in cursor.fetchall()]

# datetime 컬럼 추출
def get_datetime_column(cursor, db_name, table):
    cursor.execute("""
        SELECT COLUMN_NAME, DATA_TYPE
        FROM information_schema.columns
        WHERE table_schema = %s AND table_name = %s
    """, (db_name, table))
    datetime_columns = [row[0] for row in cursor.fetchall() if row[1].lower() in ("datetime", "timestamp")]
    for priority in ['reg_dt', 'created_at', 'insert_dt', 'log_time']:
        if priority in datetime_columns:
            return priority
    return datetime_columns[0] if datetime_columns else None

# 유효한 날짜인지 검사
def is_valid_datetime(value):
    return value not in ("0000-00-00", "0000-00-00 00:00:00", None, "")

# 날짜 범위 추출
def get_date_range(cursor, db_name, table, datetime_col):
    try:
        cursor.execute(f"""
            SELECT MIN(`{datetime_col}`), MAX(`{datetime_col}`)
            FROM `{db_name}`.`{table}`
            WHERE `{datetime_col}` IS NOT NULL
                AND `{datetime_col}` NOT IN ('0000-00-00', '0000-00-00 00:00:00')
                AND `{datetime_col}` >= '1900-01-01' AND `{datetime_col}` < '2100-01-01'
        """)
        result = cursor.fetchone()
        if not result or not is_valid_datetime(result[0]) or not is_valid_datetime(result[1]):
            return None, None
        return parser.parse(str(result[0])), parser.parse(str(result[1]))
    except:
        return None, None

# 평균 row 길이
def get_avg_row_length(cursor, db_name, table):
    cursor.execute("""
        SELECT AVG_ROW_LENGTH
        FROM information_schema.tables
        WHERE table_schema = %s AND table_name = %s
    """, (db_name, table))
    result = cursor.fetchone()
    return result[0] if result and result[0] else 0

# row 수
def get_row_count(cursor, db_name, table):
    cursor.execute(f"SELECT COUNT(*) FROM `{db_name}`.`{table}`")
    return cursor.fetchone()[0]

# 단위별 날짜 범위
def date_range_by_unit(start, end, unit):
    ranges = []
    current = start
    while current < end:
        if unit == 'week':
            next_point = current + timedelta(days=7)
            label = f"{current:%Y-W%U}"
        elif unit == 'month':
            next_point = current + relativedelta(months=1)
            label = f"{current:%Y-%m}"
        elif unit == 'year':
            next_point = current + relativedelta(years=1)
            label = f"{current:%Y}"
        else:
            raise ValueError("단위 오류")
        ranges.append((label, current, next_point))
        current = next_point
    return ranges

# 균등 분포 추정
def estimate_evenly_distributed_usage(row_count, avg_len, ranges):
    total_mb = row_count * avg_len / 1024 / 1024
    per_range_mb = total_mb / len(ranges) if ranges else 0
    return {label: f"{per_range_mb:,.3f}" for label, _, _ in ranges}, total_mb

# 날짜 컬럼 기준 용량 추정
def estimate_storage(cursor, db_name, table, datetime_col, start, end, avg_len):
    try:
        cursor.execute(f"""
            SELECT COUNT(*)
            FROM `{db_name}`.`{table}`
            WHERE `{datetime_col}` >= %s AND `{datetime_col}` < %s
                AND `{datetime_col}` NOT IN ('0000-00-00', '0000-00-00 00:00:00')
        """, (start, end))
        count = cursor.fetchone()[0]
        return round(count * avg_len / 1024 / 1024, 3)
    except:
        return 0

# 날짜 형식 포맷
def safe_date_format(date_obj):
    return date_obj.strftime('%Y-%m-%d') if hasattr(date_obj, 'strftime') else str(date_obj)

# 메인 함수
def main():
    conn, db_name = get_connection()
    cursor = conn.cursor()
    tables = get_table_list(cursor, db_name)

    result = {
        "총 테이블 수": len(tables),
        "총 테이블 명 목록": tables,
        "날짜 컬럼이 있는 테이블 수": 0,
        "날짜 컬럼이 없는 테이블 수": 0,
        "데이터가 없는 테이블 수": 0,
        "평균 row length가 0인 테이블 수": 0,
        "건너뛴 테이블 수": 0,
        "건너뛴 테이블 목록": [],
        "평균 row length가 0인 테이블 목록": [],
        "날짜 컬럼이 있는 테이블 명 목록": [],
        "날짜 컬럼이 없는 테이블 명 목록": [],
        "데이터가 없는 테이블 목록": [],
        "날짜 컬럼이 있는 테이블": {},
        "날짜 컬럼이 없는 테이블": {},
        "데이터가 없는 테이블": {},
    }

    # exclude_tables = ["md_conn_log_copy"] # 건너뛸 테이블 목록
    exclude_tables = []
    skipped_tables = []  # 건너뛴 테이블 저장용 리스트

    for table in tables:
        if table in exclude_tables:
            print(f"건너뜀: {table}")
            skipped_tables.append(table)
            result["건너뛴 테이블 수"] += 1
            continue

        try:
            print(f"분석 중: {table}")
            row_count = get_row_count(cursor, db_name, table)
            avg_len = get_avg_row_length(cursor, db_name, table)

            if row_count == 0:
                result["데이터가 없는 테이블 수"] += 1
                result["데이터가 없는 테이블 목록"].append(table)
                result["데이터가 없는 테이블"][table] = {
                    "startDate": None,
                    "endDate": None,
                    "week": {}, "month": {}, "year": {}
                }
                continue

            if avg_len == 0:
                result["평균 row length가 0인 테이블 수"] += 1
                result["평균 row length가 0인 테이블 목록"].append(table)
                continue

            datetime_col = get_datetime_column(cursor, db_name, table)

            if datetime_col:
                start_date, end_date = get_date_range(cursor, db_name, table, datetime_col)
                if not start_date or not end_date:
                    continue
                result["날짜 컬럼이 있는 테이블 수"] += 1
                result["날짜 컬럼이 있는 테이블 명 목록"].append(table)
                table_result = {
                    "startDate": safe_date_format(start_date),
                    "endDate": safe_date_format(end_date),
                    "week": {}, "month": {}, "year": {}
                }
                for unit in ['week', 'month', 'year']:
                    for label, s, e in date_range_by_unit(start_date, end_date, unit):
                        mb = estimate_storage(cursor, db_name, table, datetime_col, s, e, avg_len)
                        if mb > 0:
                            table_result[unit][label] = f"{mb:,.3f}"
                result["날짜 컬럼이 있는 테이블"][table] = table_result
            else:
                result["날짜 컬럼이 없는 테이블 수"] += 1
                result["날짜 컬럼이 없는 테이블 명 목록"].append(table)
                ranges = {
                    unit: date_range_by_unit(START_DATE_STATIC, END_DATE_STATIC, unit)
                    for unit in ['week', 'month', 'year']
                }
                table_result = {
                    "startDate": safe_date_format(START_DATE_STATIC),
                    "endDate": safe_date_format(END_DATE_STATIC),
                    "week": {}, "month": {}, "year": {}
                }
                for unit in ['week', 'month', 'year']:
                    usage, _ = estimate_evenly_distributed_usage(row_count, avg_len, ranges[unit])
                    table_result[unit] = usage
                result["날짜 컬럼이 없는 테이블"][table] = table_result

        except Exception as e:
            print(f"오류 발생: {table} → {e}")
            continue

    # 건너뛴 테이블 목록 결과에 추가
    result["건너뛴 테이블 목록"] = skipped_tables

    now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
    json_file = f"db_storage_summary_{now_str}.json"
    with open(json_file, "w", encoding="utf-8") as f:
        json.dump(result, f, indent=2, ensure_ascii=False)

    print("\n결과 요약:")
    print(json.dumps(result, indent=2, ensure_ascii=False))
    print(f"\nJSON 저장 위치: {os.path.abspath(json_file)}")

    cursor.close()
    conn.close()

if __name__ == "__main__":
    main()
DB 연결 성공: meetu_db

분석 중: account
분석 중: admin
분석 중: advertisement
분석 중: application
분석 중: bookmark
분석 중: calendarEvent
분석 중: chatMessage
분석 중: chatRoom
분석 중: communityComment
분석 중: communityLike
분석 중: communityPost
분석 중: communityTag
분석 중: company
분석 중: companyBlock
분석 중: companyFollow
분석 중: coverLetter
분석 중: coverLetterContent
분석 중: coverLetterContentFeedback
분석 중: coverLetterContentFitAnalysis
분석 중: customerSupport
분석 중: interviewReview
분석 중: jobCategory
분석 중: jobPosting
분석 중: jobPostingJobCategory
분석 중: jobPostingViewLog
분석 중: location
분석 중: notification
분석 중: offer
분석 중: payment
분석 중: profile
분석 중: resume
분석 중: resumeContent
분석 중: resumeViewLog
분석 중: systemLog

결과 요약:
{
  "총 테이블 수": 34,
  "총 테이블 명 목록": [
    "account",
    "admin",
    "advertisement",
    "application",
    "bookmark",
    "calendarEvent",
    "chatMessage",
    "chatRoom",
    "communityComment",
    "communityLike",
    "communityPost",
    "communityTag",
    "company",
    "companyBlock",
    "companyFollow",
    "coverLetter",
    "coverLetterContent",
    "coverLetterContentFeedback",
    "coverLetterContentFitAnalysis",
    "customerSupport",
    "interviewReview",
    "jobCategory",
    "jobPosting",
    "jobPostingJobCategory",
    "jobPostingViewLog",
    "location",
    "notification",
    "offer",
    "payment",
    "profile",
    "resume",
    "resumeContent",
    "resumeViewLog",
    "systemLog"
  ],
  "날짜 컬럼이 있는 테이블 수": 34,
  "날짜 컬럼이 없는 테이블 수": 0,
  "데이터가 없는 테이블 수": 0,
  "평균 row length가 0인 테이블 수": 0,
  "건너뛴 테이블 수": 0,
  "건너뛴 테이블 목록": [],
  "평균 row length가 0인 테이블 목록": [],
  "날짜 컬럼이 있는 테이블 명 목록": [
    "account",
    "admin",
    "advertisement",
    "application",
    "bookmark",
    "calendarEvent",
    "chatMessage",
    "chatRoom",
    "communityComment",
    "communityLike",
    "communityPost",
    "communityTag",
    "company",
    "companyBlock",
    "companyFollow",
    "coverLetter",
    "coverLetterContent",
    "coverLetterContentFeedback",
    "coverLetterContentFitAnalysis",
    "customerSupport",
    "interviewReview",
    "jobCategory",
    "jobPosting",
    "jobPostingJobCategory",
    "jobPostingViewLog",
    "location",
    "notification",
    "offer",
    "payment",
    "profile",
    "resume",
    "resumeContent",
    "resumeViewLog",
    "systemLog"
  ],
  "날짜 컬럼이 없는 테이블 명 목록": [],
  "데이터가 없는 테이블 목록": [],
  "날짜 컬럼이 있는 테이블": {
    "account": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-08",
      "week": {
        "2025-W14": "0.007",
        "2025-W15": "0.003",
        "2025-W16": "0.003",
        "2025-W18": "0.002"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "admin": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-08",
      "week": {},
      "month": {},
      "year": {}
    },
    "advertisement": {
      "startDate": "2025-04-17",
      "endDate": "2025-04-27",
      "week": {
        "2025-W15": "0.015",
        "2025-W16": "0.001"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "application": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-26",
      "week": {
        "2025-W14": "0.003",
        "2025-W15": "0.003",
        "2025-W16": "0.003",
        "2025-W20": "0.005"
      },
      "month": {
        "2025-04": "0.010",
        "2025-05": "0.005"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "bookmark": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-20",
      "week": {
        "2025-W14": "0.009",
        "2025-W18": "0.003",
        "2025-W20": "0.003"
      },
      "month": {
        "2025-04": "0.009",
        "2025-05": "0.006"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "calendarEvent": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-07",
      "week": {
        "2025-W14": "0.003",
        "2025-W16": "0.013",
        "2025-W17": "0.001",
        "2025-W18": "0.001"
      },
      "month": {
        "2025-04": "0.018"
      },
      "year": {
        "2025": "0.018"
      }
    },
    "chatMessage": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-20",
      "week": {
        "2025-W14": "0.003",
        "2025-W16": "0.001",
        "2025-W18": "0.005",
        "2025-W19": "0.006"
      },
      "month": {
        "2025-04": "0.004",
        "2025-05": "0.011"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "chatRoom": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-08",
      "week": {},
      "month": {},
      "year": {}
    },
    "communityComment": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-20",
      "week": {
        "2025-W14": "0.002",
        "2025-W15": "0.012",
        "2025-W16": "0.003"
      },
      "month": {
        "2025-04": "0.017"
      },
      "year": {
        "2025": "0.017"
      }
    },
    "communityLike": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-07",
      "week": {
        "2025-W14": "0.002",
        "2025-W15": "0.007",
        "2025-W16": "0.005",
        "2025-W17": "0.001",
        "2025-W18": "0.001"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "communityPost": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-27",
      "week": {
        "2025-W14": "0.001",
        "2025-W15": "0.013",
        "2025-W16": "0.003"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "communityTag": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-26",
      "week": {
        "2025-W14": "0.009",
        "2025-W16": "0.007"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "company": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-25",
      "week": {
        "2025-W14": "0.024",
        "2025-W15": "0.038",
        "2025-W16": "0.088",
        "2025-W18": "0.003",
        "2025-W20": "0.004"
      },
      "month": {
        "2025-04": "0.152",
        "2025-05": "0.004"
      },
      "year": {
        "2025": "0.156"
      }
    },
    "companyBlock": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-02",
      "week": {
        "2025-W14": "0.007",
        "2025-W16": "0.007",
        "2025-W17": "0.002"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "companyFollow": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-08",
      "week": {
        "2025-W14": "0.013",
        "2025-W16": "0.003",
        "2025-W18": "0.003"
      },
      "month": {
        "2025-04": "0.016",
        "2025-05": "0.003"
      },
      "year": {
        "2025": "0.018"
      }
    },
    "coverLetter": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-27",
      "week": {
        "2025-W14": "0.007",
        "2025-W16": "0.009"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "coverLetterContent": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-27",
      "week": {
        "2025-W14": "0.006",
        "2025-W16": "0.010"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "coverLetterContentFeedback": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-08",
      "week": {},
      "month": {},
      "year": {}
    },
    "coverLetterContentFitAnalysis": {
      "startDate": "2025-04-19",
      "endDate": "2025-04-27",
      "week": {
        "2025-W15": "0.007",
        "2025-W16": "0.010"
      },
      "month": {
        "2025-04": "0.017"
      },
      "year": {
        "2025": "0.017"
      }
    },
    "customerSupport": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-08",
      "week": {},
      "month": {},
      "year": {}
    },
    "interviewReview": {
      "startDate": "2025-04-26",
      "endDate": "2025-05-02",
      "week": {
        "2025-W16": "0.016"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "jobCategory": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-25",
      "week": {
        "2025-W14": "0.010",
        "2025-W20": "0.113"
      },
      "month": {
        "2025-04": "0.010",
        "2025-05": "0.113"
      },
      "year": {
        "2025": "0.124"
      }
    },
    "jobPosting": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-25",
      "week": {
        "2025-W14": "0.045",
        "2025-W15": "0.075",
        "2025-W16": "0.282",
        "2025-W18": "0.003",
        "2025-W20": "0.018"
      },
      "month": {
        "2025-04": "0.404",
        "2025-05": "0.018"
      },
      "year": {
        "2025": "0.422"
      }
    },
    "jobPostingJobCategory": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-25",
      "week": {
        "2025-W14": "0.001",
        "2025-W18": "0.013",
        "2025-W20": "0.001"
      },
      "month": {
        "2025-04": "0.001",
        "2025-05": "0.014"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "jobPostingViewLog": {
      "startDate": "2025-03-21",
      "endDate": "2025-03-24",
      "week": {
        "2025-W11": "0.016"
      },
      "month": {
        "2025-03": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "location": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-22",
      "week": {
        "2025-W14": "0.062"
      },
      "month": {
        "2025-04": "0.062"
      },
      "year": {
        "2025": "0.062"
      }
    },
    "notification": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-20",
      "week": {
        "2025-W14": "0.001",
        "2025-W16": "0.012",
        "2025-W19": "0.002"
      },
      "month": {
        "2025-04": "0.014",
        "2025-05": "0.002"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "offer": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-08",
      "week": {},
      "month": {},
      "year": {}
    },
    "payment": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-27",
      "week": {
        "2025-W14": "0.001",
        "2025-W15": "0.015",
        "2025-W16": "0.001"
      },
      "month": {
        "2025-04": "0.017"
      },
      "year": {
        "2025": "0.017"
      }
    },
    "profile": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-28",
      "week": {
        "2025-W14": "0.007",
        "2025-W15": "0.006",
        "2025-W16": "0.002"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "resume": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-28",
      "week": {
        "2025-W14": "0.002",
        "2025-W15": "0.010",
        "2025-W16": "0.003"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "resumeContent": {
      "startDate": "2025-04-10",
      "endDate": "2025-04-28",
      "week": {
        "2025-W14": "0.001",
        "2025-W15": "0.006",
        "2025-W16": "0.008"
      },
      "month": {
        "2025-04": "0.016"
      },
      "year": {
        "2025": "0.016"
      }
    },
    "resumeViewLog": {
      "startDate": "2025-04-08",
      "endDate": "2025-04-08",
      "week": {},
      "month": {},
      "year": {}
    },
    "systemLog": {
      "startDate": "2025-04-08",
      "endDate": "2025-05-31",
      "week": {
        "2025-W14": "0.001",
        "2025-W16": "0.002",
        "2025-W17": "0.001",
        "2025-W18": "0.020",
        "2025-W19": "0.013",
        "2025-W20": "0.007",
        "2025-W21": "0.001"
      },
      "month": {
        "2025-04": "0.013",
        "2025-05": "0.034"
      },
      "year": {
        "2025": "0.047"
      }
    }
  },
  "날짜 컬럼이 없는 테이블": {},
  "데이터가 없는 테이블": {}
}

JSON 저장 위치: d:\study\python\db_storage_summary_20250625_003654.json

추출되지 않는 테이블 명¶

In [3]:
import json

# JSON 파일 경로
file_path = 'db_storage_summary_20250624_111216.json'

# JSON 읽기
with open(file_path, 'r', encoding='utf-8') as f:
    data = json.load(f)

# 키 목록 정의
part_keys = ["날짜 컬럼이 있는 테이블 명 목록", "날짜 컬럼이 없는 테이블 명 목록", "데이터가 없는 테이블 목록"]
total_key = "총 테이블 명 목록"

# 각 키의 개수 출력
print(f"\n테이블 목록 수")
for key in part_keys + [total_key]:
    count = len(data.get(key, []))
    print(f"- {key}: {count}개")

# 부분 키들의 값 합치기 (중복 제거)
combined_set = set()
for key in part_keys:
    combined_set.update(data.get(key, []))

# 전체 목록 집합
total_set = set(data.get(total_key, []))

# 전체에 없는 값 (불일치 항목)
missing_values = combined_set - total_set

# 결과 출력
print(f"\n'총 테이블 명 목록'에 없는 값: {len(missing_values)}개")
print(missing_values)
테이블 목록 수
- 날짜 컬럼이 있는 테이블 명 목록: 159개
- 날짜 컬럼이 없는 테이블 명 목록: 26개
- 데이터가 없는 테이블 목록: 26개
- 총 테이블 명 목록: 215개

'총 테이블 명 목록'에 없는 값: 0개
set()

JSON 파일 -> 엑셀¶

In [17]:
import json
from datetime import datetime
import os
from openpyxl import Workbook

# 날짜 컬럼이 없는 경우 사용할 기준 시작일과 종료일을 설정
START_DATE_STATIC = datetime(2000, 1, 1)
END_DATE_STATIC = datetime.today()

# 분석 결과를 엑셀로 저장하는 함수
def save_excel(result_dict, filename):
    print(f">>> EXCEl 파일 저장 중...")

    wb = Workbook()

    # Sheet 1: 요약
    ws1 = wb.active
    ws1.title = "요약"
    for k, v in result_dict.items():
        if isinstance(v, list) or isinstance(v, dict):
            continue
        ws1.append([k, v])

    # 날짜 컬럼 있음 시트
    ws2 = wb.create_sheet("날짜 컬럼 있음")
    ws2.append(["테이블명", "월 단위 사용량", "년 단위 사용량", "연도 총합(MB)"])
    total_col_with_date = 0
    for table, data in result_dict["날짜 컬럼이 있는 테이블"].items():
        year_sum = sum(float(v.replace(',', '')) for v in data["year"].values())
        total_col_with_date += year_sum
        ws2.append([table, len(data["month"]), len(data["year"]), f"{year_sum:,.3f}"])
    ws2.append([])
    ws2.append(["", "", "총합", f"{total_col_with_date:,.3f}"])

    # 날짜 컬럼 없음 시트
    ws3 = wb.create_sheet("날짜 컬럼 없음")
    ws3.append(["테이블명", "월 단위 사용량", "년 단위 사용량", "연도 총합(MB)"])
    total_col_without_date = 0
    for table, data in result_dict["날짜 컬럼이 없는 테이블"].items():
        year_sum = sum(float(v.replace(',', '')) for v in data["year"].values())
        total_col_without_date += year_sum
        ws3.append([table, len(data["month"]), len(data["year"]), f"{year_sum:,.3f}"])
    ws3.append([])
    ws3.append(["", "", "총합", f"{total_col_without_date:,.3f}"])

    # 데이터 없음 시트
    ws4 = wb.create_sheet("데이터 없음")
    ws4.append(["테이블명"])
    total_empty = 0
    for table in result_dict["데이터가 없는 테이블 목록"]:
        ws4.append([table])
        total_empty += 1
    ws4.append([])
    ws4.append(["총 테이블 수", total_empty])

    # Sheet 1에 각 시트 총합 요약 추가
    ws1.append(["날짜 컬럼 있음 총합(MB)", f"{total_col_with_date:,.3f}"])
    ws1.append(["날짜 컬럼 없음 총합(MB)", f"{total_col_without_date:,.3f}"])
    ws1.append(["전체 총합(MB)", f"{total_col_with_date + total_col_without_date:,.3f}"])

    wb.save(filename)

# 메인 함수
def main():
    json_input_path = "db_storage_summary_20250625_002141.json"

    if not os.path.exists(json_input_path):
        print(f"파일이 존재하지 않음: {json_input_path}")
        return

    with open(json_input_path, "r", encoding="utf-8") as f:
        result = json.load(f)

    print("\n분석 결과 로드 완료.")
    print("샘플 키 목록:", list(result.keys())[:5])  # 출력 축소 또는 생략

    now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
    excel_file = f"db_storage_summary_{now_str}.xlsx"

    save_excel(result, excel_file)

    print(f"Excel 저장 완료: {os.path.abspath(excel_file)}")

if __name__ == "__main__":
    main()
파일이 존재하지 않음: db_storage_summary_20250625_002141.json

JSON 파일 -> HTML¶

In [19]:
# 필요한 라이브러리 불러오기
import json  # JSON 파일을 읽고 쓰기 위한 표준 라이브러리
from datetime import datetime  # 현재 날짜와 시간을 가져오기 위한 라이브러리
import os  # 파일 경로 및 존재 여부를 확인하기 위한 라이브러리
from IPython.display import display, HTML  # Jupyter Notebook에서 HTML을 출력하기 위한 라이브러리
import plotly.graph_objs as go  # Plotly에서 그래프(도형, 선 등)를 구성하기 위한 객체들
from plotly.offline import plot  # HTML 안에 그래프를 그리기 위한 함수

# 각 테이블에서 연도별 데이터를 추출하는 함수
def extract_yearly_data_per_table(data_dict):
    per_table_data = {}  # 테이블별 결과를 저장할 딕셔너리
    for table, data in data_dict.items():  # 테이블 이름과 해당 데이터 반복
        year_data = {}  # 연도별 데이터를 저장할 딕셔너리
        for year, val in data["year"].items():  # 연도와 그 해의 값 반복
            year_data[year] = float(val.replace(',', ''))  # 쉼표 제거 후 숫자로 변환
        per_table_data[table] = year_data  # 테이블명에 연도별 데이터 저장
    return per_table_data  # 전체 테이블별 연도 데이터 반환

# HTML 파일을 생성하고 저장하는 함수
def save_html(result_dict, filename):
    today_str = datetime.today().strftime('%Y-%m-%d')  # 오늘 날짜를 'YYYY-MM-DD' 형식으로 저장

    # JSON 데이터에서 날짜 컬럼이 있는 테이블과 없는 테이블의 연도별 데이터를 각각 추출
    with_date_tables = extract_yearly_data_per_table(result_dict["날짜 컬럼이 있는 테이블"])
    without_date_tables = extract_yearly_data_per_table(result_dict["날짜 컬럼이 없는 테이블"])

    # 테이블 목록을 HTML 표 형태로 만드는 함수 (가로 방식: 테이블명을 열로, 값은 한 줄로 출력)
    def make_table(title, table_dict):
        headers = "".join(f"<th>{k}</th>" for k in table_dict.keys())  # 테이블명을 <th> 태그로 생성
        values = "".join(f"<td>{v:.3f}</td>" for v in table_dict.values())  # 용량 값을 소수점 3자리까지 표현
        return f"""
        <details open>
            <summary>{title} (총합: {sum(table_dict.values()):.3f} MB)</summary>
            <table style='width:auto;'>
                <tr>{headers}</tr>
                <tr>{values}</tr>
            </table>
        </details>
        """

    # 테이블 용량 요약 정보 계산 (합계)
    total_with_date = sum(sum(tbl.values()) for tbl in with_date_tables.values())  # 날짜 컬럼 있는 테이블 총합
    total_without_date = sum(sum(tbl.values()) for tbl in without_date_tables.values())  # 날짜 컬럼 없는 테이블 총합

    # 각 테이블별로 연도 데이터를 모두 합쳐서 테이블 단위로 합계만 계산
    table_with_date_dict = {k: sum(v.values()) for k, v in with_date_tables.items()}
    table_without_date_dict = {k: sum(v.values()) for k, v in without_date_tables.items()}
    table_empty_dict = {k: 0 for k in result_dict['데이터가 없는 테이블']}  # 데이터가 아예 없는 테이블은 0MB로 처리

    # 요약 정보를 담은 HTML 표 생성
    summary_html = f"""
        <details open>
            <summary>1. 요약</summary>
            <table>
                <tr><th>항목</th><th>값</th></tr>
                <tr><td>총 테이블 수</td><td>{result_dict['총 테이블 수']}</td></tr>
                <tr><td>날짜 컬럼이 있는 테이블 수</td><td>{result_dict['날짜 컬럼이 있는 테이블 수']}</td></tr>
                <tr><td>날짜 컬럼이 없는 테이블 수</td><td>{result_dict['날짜 컬럼이 없는 테이블 수']}</td></tr>
                <tr><td>데이터가 없는 테이블 수</td><td>{result_dict['데이터가 없는 테이블 수']}</td></tr>
                <tr><td>날짜 컬럼 있음 총합(MB)</td><td>{total_with_date:.3f}</td></tr>
                <tr><td>날짜 컬럼 없음 총합(MB)</td><td>{total_without_date:.3f}</td></tr>
                <tr><td>전체 총합(MB)</td><td>{total_with_date + total_without_date:.3f}</td></tr>
            </table>
        </details>
    """

    # Plotly 그래프를 HTML 형식으로 생성하는 함수
    def generate_plot_html(table_dict, title):
        traces = []  # 그래프에 들어갈 데이터 라인을 담을 리스트
        # 라인 색상을 구분하기 위한 컬러 팔레트
        palette = ["#1f77b4", "#ff7f0e", "#2ca02c", "#d62728", "#9467bd",
                   "#8c564b", "#e377c2", "#7f7f7f", "#bcbd22", "#17becf"]
        for idx, (table, year_map) in enumerate(table_dict.items()):
            traces.append(go.Scatter(  # 선 그래프(trace) 하나 생성
                x=list(year_map.keys()),  # x축: 연도
                y=list(year_map.values()),  # y축: 해당 연도 용량
                mode='lines+markers',  # 선과 점 모두 표시
                name=table,  # 범례에 표시할 이름
                line=dict(color=palette[idx % len(palette)])  # 색상은 반복적으로 순환
            ))
        fig = go.Figure(data=traces)  # 그래프 생성
        fig.update_layout(title=title, xaxis_title='연도', yaxis_title='용량 (MB)')  # 그래프 제목과 축 라벨 설정
        return plot(fig, include_plotlyjs='inline', output_type='div')  # JS를 HTML 내부에 포함하여 그래프를 div로 반환

    # 날짜 컬럼 있는 테이블용 그래프 HTML 생성
    chart_with_date_html = generate_plot_html(with_date_tables, "테이블별 연도별 사용량 (날짜 컬럼 있음)")
    # 날짜 컬럼 없는 테이블용 그래프 HTML 생성
    chart_without_date_html = generate_plot_html(without_date_tables, "테이블별 연도별 사용량 (날짜 컬럼 없음)")

    # 전체 HTML 페이지 구성
    html = f"""
    <!DOCTYPE html>
    <html lang="ko">
    <head>
        <meta charset="UTF-8">
        <title>DB 테이블 용량 분석 결과</title>
        <style>
            body {{ font-family: Arial, sans-serif; padding: 20px; }}
            summary {{ cursor: pointer; font-weight: bold; margin-top: 10px; }}
            table {{ border-collapse: collapse; margin: 10px 0; }}
            th, td {{ border: 1px solid #ccc; padding: 6px 10px; font-size: 13px; }}
            div[id$='Chart'] {{ max-width: 100%; height: 400px; overflow-x: auto; }}
        </style>
    </head>
    <body>
        <h1>DB 테이블 용량 분석 결과 ({today_str})</h1>
        {summary_html}  <!-- 요약 정보 출력 -->

        <details open><summary>2. 날짜 컬럼 있음 - 테이블별 연도별</summary>{chart_with_date_html}</details>
        <details open><summary>3. 날짜 컬럼 없음 - 테이블별 연도별</summary>{chart_without_date_html}</details>

        {make_table("4. 날짜 컬럼이 있는 테이블 명", table_with_date_dict)}
        {make_table("5. 날짜 컬럼이 없는 테이블 명", table_without_date_dict)}
        {make_table("6. 데이터가 없는 테이블 명", table_empty_dict)}
    </body>
    </html>
    """

    # 위에서 생성한 HTML을 파일로 저장
    with open(filename, "w", encoding="utf-8") as f:
        f.write(html)

    print(f"HTML 저장 완료: {os.path.abspath(filename)}")  # 저장된 파일 경로 출력

    # Jupyter Notebook에서 바로 확인할 수 있도록 HTML을 출력
    display(HTML(html))

# 메인 함수: 전체 실행의 시작점
def main():
    json_file = "db_storage_summary_20250625_003654.json"  # 분석할 JSON 파일명

    # 파일이 실제 존재하는지 확인
    if not os.path.exists(json_file):
        print(f"JSON 파일이 존재하지 않음: {json_file}")
        return

    # JSON 파일 읽기
    with open(json_file, "r", encoding="utf-8") as f:
        result = json.load(f)

    # 현재 시각을 기준으로 HTML 파일 이름 생성
    now_str = datetime.now().strftime('%Y%m%d_%H%M%S')
    html_file = f"db_storage_summary_{now_str}.html"

    # HTML 생성 및 출력 함수 실행
    save_html(result, html_file)

# main() 함수를 직접 실행하도록 설정 (스크립트 단독 실행 시에만 동작)
if __name__ == "__main__":
    main()
HTML 저장 완료: d:\study\python\db_storage_summary_20250625_003707.html
DB 테이블 용량 분석 결과

DB 테이블 용량 분석 결과 (2025-06-25)

1. 요약
항목값
총 테이블 수34
날짜 컬럼이 있는 테이블 수34
날짜 컬럼이 없는 테이블 수0
데이터가 없는 테이블 수0
날짜 컬럼 있음 총합(MB)1.186
날짜 컬럼 없음 총합(MB)0.000
전체 총합(MB)1.186
2. 날짜 컬럼 있음 - 테이블별 연도별
3. 날짜 컬럼 없음 - 테이블별 연도별
4. 날짜 컬럼이 있는 테이블 명 (총합: 1.186 MB)
accountadminadvertisementapplicationbookmarkcalendarEventchatMessagechatRoomcommunityCommentcommunityLikecommunityPostcommunityTagcompanycompanyBlockcompanyFollowcoverLettercoverLetterContentcoverLetterContentFeedbackcoverLetterContentFitAnalysiscustomerSupportinterviewReviewjobCategoryjobPostingjobPostingJobCategoryjobPostingViewLoglocationnotificationofferpaymentprofileresumeresumeContentresumeViewLogsystemLog
0.0160.0000.0160.0160.0160.0180.0160.0000.0170.0160.0160.0160.1560.0160.0180.0160.0160.0000.0170.0000.0160.1240.4220.0160.0160.0620.0160.0000.0170.0160.0160.0160.0000.047
5. 날짜 컬럼이 없는 테이블 명 (총합: 0.000 MB)
6. 데이터가 없는 테이블 명 (총합: 0.000 MB)

특정 키 값을 엑셀로 저장¶

In [ ]:
import json
import pandas as pd

# 1. JSON 읽기
with open('db_storage_summary_20250624_111216.json', 'r', encoding='utf-8') as f:
    json_data = json.load(f)

# 2. ',' 기준으로 분리
raw_list = json_data['총 테이블 명 목록']
rows = []

for line in raw_list:
    parts = [part.strip() for part in line.split(',')]
    for idx, name in enumerate(parts, start=1):
        rows.append({'순번': idx, '테이블명': name})

# 3. 엑셀로 저장
df = pd.DataFrame(rows)
df.to_excel("output.xlsx", index=False)

# 결과값 출력
df
Out[ ]:
순번 테이블명
0 1 MDCL_RSV
1 1 SC_LOG
2 1 SC_TRAN
3 1 Surveys
4 1 _tiaraEvent
... ... ...
210 1 user_token_use
211 1 yb_pormotion
212 1 yb_randing
213 1 zzAnswerB
214 1 zzEstimate

215 rows × 2 columns